using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
//using access = Microsoft.Office.Interop.Access;
using outlook = Microsoft.Office.Interop.Outlook;

namespace ESnatch
{
    public class AccessDatabase
    {
       /* access.Application m_app;               
        dao.Database m_db;
        string m_tableName;
        string m_dbPath;
        */
        public AccessDatabase()
        {
          //  m_app = new access.Application();
        }
        /*
        public void releaseAccess()
        {            
            m_app.Quit(access.AcQuitOption.acQuitSaveAll);            
        }

        private dao.DataTypeEnum getAccessFieldType(string targType)
        {    
            switch(targType)
            {
                case "Text":
                    return dao.DataTypeEnum.dbText;
                case "Memo":
                    return dao.DataTypeEnum.dbMemo;
                case "Date/Time":
                    return dao.DataTypeEnum.dbDate;
                case "Integer":
                    return dao.DataTypeEnum.dbInteger;
                case "Yes/No":
                    return dao.DataTypeEnum.dbBoolean;
                default:
                    return dao.DataTypeEnum.dbText;
            }
        }

        public object getAccessFieldSize(string targType)
        {
            switch (targType)
            {
                case "Text":
                    return 255;
                case "Memo":
                    return Type.Missing;
                case "Date/Time":
                    return Type.Missing;
                case "Integer":
                    return Type.Missing;
                case "Yes/No":
                    return Type.Missing;
                default:
                    return Type.Missing;
            }
        }

        public void createNewDatabase(string pathToDb, string tableName,
                                        List<OutputMapping> mappings)
        {
            m_app.NewCurrentDatabase(pathToDb);
            dao.Database  db = m_app.CurrentDb();
            object dummy = Type.Missing;
            dao.TableDef tblDef = db.CreateTableDef(tableName, dao.TableDefAttributeEnum.dbAttachExclusive, "", "");
            foreach(OutputMapping mapping in mappings)
            {
                dao.DataTypeEnum adt = getAccessFieldType(mapping.TargetType);
                object fldSize = getAccessFieldSize(mapping.TargetType);
                dao.Field field = tblDef.CreateField(mapping.SrcPropInfo.Name, 
                    adt, fldSize);                    
                tblDef.Fields.Append(field);
            }
            db.TableDefs.Append(tblDef);
            m_tableName = tableName;
            m_db = db;
            m_db.Close();
            m_app.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);
            m_db = null;
            m_app = null;

            m_dbPath = pathToDb;
        }

        

        protected void assignCommandText(ADODB.Command cmd, List<OutputMapping> mappings)
        {
            bool bFirst = true;
            StringBuilder sb = new StringBuilder();
            StringBuilder sbFieldList = new StringBuilder();
            StringBuilder sbValList = new StringBuilder();
            sb.Append("INSERT INTO (");
            sb.Append(m_tableName);
            sb.Append("(");
            foreach (OutputMapping mapping in mappings)
            {
                if (mapping.Include)
                {
                    if (!bFirst)
                    {
                        sbFieldList.Append(", ");
                        sbValList.Append(", ");
                    }
                    else
                        bFirst = false;
                    
                    sbFieldList.Append(mapping.SrcPropInfo.Name);
                    sbValList.Append("@" + mapping.SrcPropInfo.Name);
                }
            }            
            sb.Append(sbFieldList.ToString());
            sb.Append(") VALUES (");
            sb.Append(sbValList.ToString());
            sb.Append(")");
            cmd.CommandText = sb.ToString();
        }
        
        / *
        Microsoft Access Data Type     ADO Equivalent
            Binary                         adBinary
            Boolean                        adBoolean
            Byte                           adUnsignedTinyInt
            Currency                       adCurrency
            Date                           adDate
            Numeric                        adNumeric
            Double                         adDouble
            Small Integer                  adSmallInt
            Integer                        adInteger
            Long Binary                    adLongBinary
            Memo                           adLongVarWChar
            Single                         adSingle
            Text                           adWChar
        * /
        protected ADODB.DataTypeEnum getDataTypeForTargtype(string targType)
        {
            switch (targType)
            {
                case "Text":
                    return ADODB.DataTypeEnum.adWChar;
                case "Date/Time":
                    return ADODB.DataTypeEnum.adDBTimeStamp;
                case "Integer":
                    return ADODB.DataTypeEnum.adInteger;
                case "Memo":
                    return ADODB.DataTypeEnum.adLongVarWChar;
                case "Yes/No":
                    return ADODB.DataTypeEnum.adBoolean;
            }
            return ADODB.DataTypeEnum.adPropVariant;
        }

        protected int getParamSizeForTargtype(string targType, object propVal)
        {
            switch (targType)
            {
                case "Text":
                    {
                        string s = (string)propVal;
                        if (s.Length < 1)
                            return 1;
                        return s.Length;
                    }
                case "Date/Time":
                    return 4;
                case "Integer":
                    return sizeof(int);
                case "Memo":
                    {
                        string s = (string)propVal;
                        if (s.Length < 1)
                            return 1;
                        return s.Length;
                    }
                case "Yes/No":
                    return sizeof(bool);
            }
            return 0;
        }
        
        protected void setCommandParameterValues(ADODB.Command cmd, List<OutputMapping> mappings,
                                            outlook.MailItem mailItem)
        {
            foreach (OutputMapping mapping in mappings)
            {
                //cmd.Parameters.Refresh();
                if (mapping.Include)
                {
                    ADODB.Parameter parm;
                    //Get the worksheets collection.
                    //   sheets = book.Worksheets;
                    //Object sheets = book.GetType().InvokeMember("Worksheets",
                    //      BindingFlags.GetProperty, null, book, null);
                    string propname = mapping.SrcPropInfo.Name;
                    string parmName = "@" + propname;
                    ADODB.DataTypeEnum dt = getDataTypeForTargtype(mapping.TargetType);
                    ADODB.ParameterDirectionEnum direction = ADODB.ParameterDirectionEnum.adParamInput;
                    Object propVal = mailItem.GetType().InvokeMember(propname,
                                    BindingFlags.GetProperty, null, mailItem, null);
                    int size = getParamSizeForTargtype(mapping.TargetType, propVal);
                    
                  //  ADODB.Parameter parm = cmd.Parameters[parmName];
                    parm = cmd.CreateParameter(parmName, dt, direction, size, propVal);
                    cmd.Parameters.Append(parm);                        
                }
            }
        }
                
        public void persistMailFolder(List<OutputMapping> mappings,
                                        outlook.MAPIFolder folder)
        {
            
            ADODB.Connection cnnDB = new ADODB.Connection();
            ADODB.Command cmd = new ADODB.Command();

            // Specify Microsoft Jet 4.0 Provider and then open the
            // database specified in the strDBPath variable.
            cnnDB.Provider = "Microsoft.Jet.OLEDB.4.0";
            cnnDB.Open(m_dbPath, null, null, 0);
            cmd.CommandType = ADODB.CommandTypeEnum.adCmdTable;
            cmd.ActiveConnection = cnnDB;
            assignCommandText(cmd, mappings);
            //assignCommandParameters(cmd, mappings);

            // Now iterate through the folder and persist each item to the
            //database.
            foreach(outlook.MailItem mailitem in folder.Items)
            {
                object dummy = Type.Missing;
                setCommandParameterValues(cmd, mappings, mailitem);
                cmd.Execute(out dummy, ref dummy, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
            }
            // Close Connection object and destroy object variable.
            cnnDB.Close();
            cnnDB = null;
        }
        */
    }
}
